﻿Imports System.Data.SqlClient
Imports DTO
Imports System.ComponentModel

Public Class NhomNDDAO

    Shared Function layDsNhomND() As BindingList(Of NhomNDDTO)
        Dim dsNhom As New BindingList(Of NhomNDDTO)
        Dim cn As SqlConnection = CKetNoi.Connect
        cn.Open()
        Dim cmd As New SqlCommand
        cmd.CommandText = "usp_LayDSNhomND"
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = cn
        Dim reader As SqlDataReader = cmd.ExecuteReader
        While reader.Read
            Dim nhom As New NhomNDDTO
            nhom.MaNhom = reader("MaNhom")
            nhom.TenNhom = reader("TenNhom")
            nhom.DienGiai = reader("DienGiai")
            nhom.KichHoat = reader("KichHoat")
            dsNhom.Add(nhom)
        End While
        cn.Close()
        Return dsNhom
    End Function

    Shared Function layDsVaiTro() As DataTable
        Return CKetNoi.GetDataTableByStoreProc("usp_LayDsNhom")
    End Function

    Shared Function themNhom(nhom As NhomNDDTO) As String
        Dim kq As String
        Dim cn As SqlConnection = CKetNoi.Connect
        Dim cmd As New SqlCommand
        cn.Open()
        cmd.CommandText = "usp_ThemNhom"
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = cn

        Dim para As SqlParameter
        para = New SqlParameter("@TenNhom", SqlDbType.NVarChar, 100)
        para.Direction = ParameterDirection.Input
        para.Value = nhom.TenNhom
        cmd.Parameters.Add(para)

        para = New SqlParameter("@DienGiai", SqlDbType.NVarChar, 255)
        para.Direction = ParameterDirection.Input
        para.Value = nhom.DienGiai
        cmd.Parameters.Add(para)

        para = New SqlParameter("@KichHoat", SqlDbType.Bit)
        para.Direction = ParameterDirection.Input
        para.Value = nhom.KichHoat
        cmd.Parameters.Add(para)


        para = New SqlParameter("@MaNhom", SqlDbType.VarChar, 6)
        para.Direction = ParameterDirection.Output
        cmd.Parameters.Add(para)

        cmd.ExecuteNonQuery()
        kq = cmd.Parameters("@MaNhom").Value.ToString


        cn.Close()
        Return kq
    End Function

    Shared Sub phanQuyenChoNhom(dsQuyenNhomND As List(Of PhanQuyenNhonNDDTO), maNhom As String)
        Dim cn As SqlConnection = CKetNoi.Connect
        cn.Open()
        For Each pq As PhanQuyenNhonNDDTO In dsQuyenNhomND
            Dim cmd As New SqlCommand
            cmd.CommandText = "usp_PhanQuyenNhomND"
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Connection = cn
            Dim para As SqlParameter
            para = New SqlParameter("@MaNhom", SqlDbType.VarChar, 6)
            para.Direction = ParameterDirection.Input
            para.Value = pq.MaNhom
            cmd.Parameters.Add(para)

            para = New SqlParameter("@MaChucNang", SqlDbType.Int)
            para.Direction = ParameterDirection.Input
            para.Value = pq.MaChucNang
            cmd.Parameters.Add(para)

            para = New SqlParameter("@TatCa", SqlDbType.Bit)
            para.Direction = ParameterDirection.Input
            para.Value = pq.TatCa
            cmd.Parameters.Add(para)

            para = New SqlParameter("@TruyCap", SqlDbType.Bit)
            para.Direction = ParameterDirection.Input
            para.Value = pq.TruyCap
            cmd.Parameters.Add(para)

            para = New SqlParameter("@Xoa", SqlDbType.Bit)
            para.Direction = ParameterDirection.Input
            para.Value = pq.Xoa
            cmd.Parameters.Add(para)

            para = New SqlParameter("@Sua", SqlDbType.Bit)
            para.Direction = ParameterDirection.Input
            para.Value = pq.Sua
            cmd.Parameters.Add(para)

            para = New SqlParameter("@In", SqlDbType.Bit)
            para.Direction = ParameterDirection.Input
            para.Value = pq.InPQ
            cmd.Parameters.Add(para)

            para = New SqlParameter("@Nhap", SqlDbType.Bit)
            para.Direction = ParameterDirection.Input
            para.Value = pq.Nhap
            cmd.Parameters.Add(para)

            para = New SqlParameter("@Xuat", SqlDbType.Bit)
            para.Direction = ParameterDirection.Input
            para.Value = pq.Xuat
            cmd.Parameters.Add(para)

            para = New SqlParameter("@Them", SqlDbType.Bit)
            para.Direction = ParameterDirection.Input
            para.Value = pq.Them
            cmd.Parameters.Add(para)

            cmd.ExecuteNonQuery()
        Next
        cn.Close()
    End Sub

    Shared Function layDsNhomNDBangMa(maNhom As String) As NhomNDDTO
        Dim nhom As New NhomNDDTO
        Dim cn As SqlConnection = CKetNoi.Connect
        Dim cmd As New SqlCommand
        cn.Open()
        cmd.CommandText = "usp_LayNhomNguoiDungBangMa"
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = cn

        Dim para As SqlParameter
        para = New SqlParameter("@MaNhom", SqlDbType.VarChar, 6)
        para.Direction = ParameterDirection.Input
        para.Value = maNhom
        cmd.Parameters.Add(para)

        Dim read As SqlDataReader = cmd.ExecuteReader()
        If read.Read Then
            nhom.MaNhom = read("MaNhom")
            nhom.TenNhom = read("TenNhom")
            nhom.DienGiai = read("DienGiai")
            nhom.KichHoat = read("KichHoat")
        End If
        cn.Close()
        Return nhom
    End Function

    Shared Function CapNhatNhom(nhom As NhomNDDTO) As String
        Dim dsVal As New List(Of SqlParameter)
        Dim para As SqlParameter

        para = New SqlParameter("@MaNhom", SqlDbType.VarChar, 6)
        para.Direction = ParameterDirection.Input
        para.Value = nhom.MaNhom
        dsVal.Add(para)

        para = New SqlParameter("@TenNhom", SqlDbType.NVarChar, 100)
        para.Direction = ParameterDirection.Input
        para.Value = nhom.TenNhom
        dsVal.Add(para)

        para = New SqlParameter("@DienGiai", SqlDbType.NVarChar, 255)
        para.Direction = ParameterDirection.Input
        para.Value = nhom.DienGiai
        dsVal.Add(para)

        para = New SqlParameter("@KichHoat", SqlDbType.Bit)
        para.Direction = ParameterDirection.Input
        para.Value = nhom.KichHoat
        dsVal.Add(para)

        Dim kq As Integer = CKetNoi.ExecQueryStoreProc("usp_CapNhatNhomND", dsVal)
        Return kq
    End Function

    Shared Sub CapNhatPhanQuyen(dsQuyenNhomND As List(Of PhanQuyenNhonNDDTO), maNhom As String)
        Dim cn As SqlConnection = CKetNoi.Connect
        cn.Open()
        For Each pq As PhanQuyenNhonNDDTO In dsQuyenNhomND
            Dim cmd As New SqlCommand
            cmd.CommandText = "usp_CapNhatPhanQuyenNhomND"
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Connection = cn
            Dim para As SqlParameter
            para = New SqlParameter("@MaNhom", SqlDbType.VarChar, 6)
            para.Direction = ParameterDirection.Input
            para.Value = maNhom
            cmd.Parameters.Add(para)

            para = New SqlParameter("@MaChucNang", SqlDbType.Int)
            para.Direction = ParameterDirection.Input
            para.Value = pq.MaChucNang
            cmd.Parameters.Add(para)

            para = New SqlParameter("@TatCa", SqlDbType.Bit)
            para.Direction = ParameterDirection.Input
            para.Value = pq.TatCa
            cmd.Parameters.Add(para)

            para = New SqlParameter("@TruyCap", SqlDbType.Bit)
            para.Direction = ParameterDirection.Input
            para.Value = pq.TruyCap
            cmd.Parameters.Add(para)

            para = New SqlParameter("@Xoa", SqlDbType.Bit)
            para.Direction = ParameterDirection.Input
            para.Value = pq.Xoa
            cmd.Parameters.Add(para)

            para = New SqlParameter("@Sua", SqlDbType.Bit)
            para.Direction = ParameterDirection.Input
            para.Value = pq.Sua
            cmd.Parameters.Add(para)

            para = New SqlParameter("@In", SqlDbType.Bit)
            para.Direction = ParameterDirection.Input
            para.Value = pq.InPQ
            cmd.Parameters.Add(para)

            para = New SqlParameter("@Nhap", SqlDbType.Bit)
            para.Direction = ParameterDirection.Input
            para.Value = pq.Nhap
            cmd.Parameters.Add(para)

            para = New SqlParameter("@Xuat", SqlDbType.Bit)
            para.Direction = ParameterDirection.Input
            para.Value = pq.Xuat
            cmd.Parameters.Add(para)

            para = New SqlParameter("@Them", SqlDbType.Bit)
            para.Direction = ParameterDirection.Input
            para.Value = pq.Them
            cmd.Parameters.Add(para)

            cmd.ExecuteNonQuery()
        Next
        cn.Close()
    End Sub

    Shared Function LayDSNguoiDung() As BindingList(Of NguoiDungDTO)
        Dim dsND As New BindingList(Of NguoiDungDTO)
        Dim cn As SqlConnection = CKetNoi.Connect
        cn.Open()
        Dim cmd1 As New SqlCommand
        cmd1.CommandText = "usp_layDsNguoiDung"
        cmd1.CommandType = CommandType.StoredProcedure
        cmd1.Connection = cn

        Dim reader1 As SqlDataReader = cmd1.ExecuteReader
        While reader1.Read
            Dim nd As New NguoiDungDTO
            nd.MaNguoiDung = reader1("MaNguoiDung")
            nd.MaNhanVien = reader1("MaNhanVien")
            nd.MaNhom = reader1("MaNhom")
            nd.TaiKhoan = reader1("TaiKhoan")
            nd.MaTrangThai = reader1("MaTrangThai")
            nd.DienGiai = reader1("DienGiai")
            dsND.Add(nd)
        End While
        cn.Close()
        Return dsND
    End Function

End Class
